/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ptit.subject;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;

/**
 *
 * @author Admin
 */
public class Subject {
    static Connection conn =null;
    static Statement stm;
    private  String subject_name;
    private int score_1_ratio,score_2_ratio,score_3_ratio;    
    private String subject_id;
    private String stt_id;
    private int number_of_section;
    
    public void setStt_id (String stt_id)
    {
        this.stt_id = stt_id;
    }
    public String getStt_id()
    {
        return stt_id;
    }
    
    public Subject()
    {
        
    }
    public Subject(String subject_name,String subject_id, 
            int score_1_ratio,int score_2_ratio,int score_3_ratio,int number_of_section)
    {
        this.subject_name=subject_name;
        this.score_1_ratio=score_1_ratio;
        this.score_2_ratio=score_2_ratio;
        this.score_3_ratio=score_3_ratio;
        this.subject_id = subject_id;
        this.number_of_section=number_of_section;        
    }
    
    public  void connect(){
        try{
        String userConn ="root";
        String passConn = "tyty2629";
        String url = "jdbc:mysql://127.0.0.1:3306/mangementscore";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn =  (Connection) DriverManager.getConnection(url, userConn, passConn);
        System.out.println("Da ket noi CSDL");
        stm = (Statement) conn.createStatement();
        }
        catch(Exception e)
        {
        System.out.println(e.toString());
         }
    }

    public  void disconnect() throws SQLException  {
        stm.close();
        conn.close();
    }
     public void addTitle(StringBuilder sb)
   {
            sb.append("<tr>");
            sb.append("<th>");
            sb.append("Tên môn học ");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Mã môn học");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Hệ số chuyên cần");
            sb.append("</th>"); 
            sb.append("<th>");
            sb.append("Hệ số giữa kì");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Hệ số cuối kì");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Số tín chỉ");
            sb.append("</th>");
          
           
            sb.append("<th>");
            sb.append("Xóa");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Sửa");
            sb.append("</th>");
            sb.append("</tr>");
   }
    public String showAll()
    {
       String allStudents = "";
        StringBuilder sb = new StringBuilder();
        try {
       
            ResultSet rs = stm.executeQuery("select * from subject");
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            sb.append("<table border>");
            addTitle(sb);
            while (rs.next()) {
                sb.append("<tr>");
                for (int i = 1; i < rsmd.getColumnCount(); i++) {
                    sb.append("<td>");
                    sb.append(rs.getString(i + 1));
                    sb.append("</td>");
                }                         
               
                 sb.append("<td><a href=\"Controller?requestAction=deleteSubject#stt_id=")
                         .append(rs.getString(1)).append("\"><img src=\"Images/Delete32.png\"></a></td>");       
                 sb.append("<td><a href=\"EditSubject.jsp?subjectID=")
                         .append(rs.getString(1)).append("#subject_name=").append(rs.getString(2))
                         .append("#subject_id=").append(rs.getString(3))
                         .append("#score_1_ratio=").append(rs.getString(4))
                         .append("#score_2_ratio=").append(rs.getString(5))
                         .append("#score_3_ratio=").append(rs.getString(6))
                         .append("#stt_id=").append(rs.getString(1))  
                         .append("#number_of_section=").append(rs.getString(7))
                         .append("\"><img src=\"Images/Student_edit32.png\"></a></td>");
                  sb.append("</tr>");
            }

            sb.append("</table>");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        allStudents = sb.toString();        
        return allStudents;
    }

    public void addSubject() throws SQLException {
     connect();
    String cmdInsert = "INSERT INTO subject(subject_name,score_1_ratio,score_2_ratio,score_3_ratio,subject_id,number_of_section) VALUES ("
            + "'"+  subject_name+"'"+ ","      
            + "'" + score_1_ratio + "'" +","
            + "'" + score_2_ratio + "'"+","
            + "'" + score_3_ratio + "'"+","
            + "'" + subject_id + "'"+","
            + "'" + number_of_section + "'" +");";
    
    stm.executeUpdate(cmdInsert);
    disconnect();
    }
    public void editSubject() throws SQLException {
        connect();
        String cmdUpdate ="UPDATE `mangementscore`. `subject` SET   `subject_name`='"  + subject_name   
                + "',subject_id='" + subject_id
                + "',score_1_ratio='" + score_1_ratio
                + "',score_2_ratio='" + score_2_ratio
                + "',score_3_ratio='" + score_3_ratio 
               + "',number_of_section='" + number_of_section 
                +"'  where  stt_id LIKE "+ stt_id+";";
        stm.executeUpdate(cmdUpdate);
        disconnect();
    }

    public void deleteSubject(String stt_idDelete) throws SQLException {
        connect();
        String cmdDelete = "Delete from subject where stt_id =  '"+stt_idDelete+"';";
        stm.executeUpdate(cmdDelete);
        disconnect();
    }
    

}
